package com.eascs.web.o2o.scheduler.history;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.ServiceDao;
import com.eascs.web.o2o.scheduler.entity.HrUnit;
import com.eascs.web.o2o.scheduler.entity.LoanBaseInfoEntity;
import com.eascs.workflow.common.service.sys.SysOrgService;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.stereotype.Service;

import javax.inject.Inject;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

@Service
public class LoanBaseInfoService extends BaseService {
	protected final Logger logger = LoggerFactory.getLogger(getClass());
	@Inject
	BaseDao baseDao;
	@Inject
	ServiceDao  serviceDao;
	@Reference
	private SysOrgService sysOrgService;
	
	
	@Deprecated
	public void save()  throws Exception{
		String querySql = "sql.loanbaseinfo";
		String delSql	= "DELETE FROM loan_base_info where source_type='0'";
				
		baseDao.deleteEntity(delSql); 
		int pageIndex=0;
		int pageSize=100;
		while(pageIndex>=0){
			logger.info("loanBaseInfoTask定时任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<LoanBaseInfoEntity> sqlPageQuery = new SqlPageQuery<>(LoanBaseInfoEntity.class, pageSize,pageIndex, querySql);
			PageResult<LoanBaseInfoEntity> results = baseDao.findDataWithPage(sqlPageQuery);
			
			List<LoanBaseInfoEntity> baseInfoList = results.getData();
			for(LoanBaseInfoEntity baseInfo : baseInfoList) {
				String sql="SELECT t.billstatus as is_place_on_file FROM eadb.o2o_refundcontract t LEFT JOIN eadb.o2o_refund r ON t.refundno = r.refundno WHERE t.isfrom = 'CON' and  r.islock='N' and t.islock='N' and r.loanapplyno=?";
				LoanBaseInfoEntity entity=	baseDao.findEntity(sql, LoanBaseInfoEntity.class, baseInfo.getLoanApplyNo());
				if (entity != null) {
					baseInfo.setIsPlaceOnFile(entity.getIsPlaceOnFile());// 归档状态
					if ("CLO".equals(entity.getIsPlaceOnFile())) {
						sql = "SELECT max(p.auditordate) AS place_on_file_time FROM eadb.wf_processtaskhistory p WHERE billno = ( SELECT t.refundcontractno FROM eadb.o2o_refundcontract t LEFT JOIN eadb.o2o_refund r ON t.refundno = r.refundno WHERE t.isfrom = 'CON' AND t.billstatus = 'CLO' and r.islock='N' and t.islock='N' AND r.loanapplyno =? ) AND billtype = 'RCM'";
						entity = baseDao.findEntity(sql, LoanBaseInfoEntity.class, baseInfo.getLoanApplyNo());
						if (entity != null && entity.getPlaceOnFileTime() != null) {
							baseInfo.setPlaceOnFileTime(entity.getPlaceOnFileTime());// 归档完成时间
						}
					}
				}

				baseInfo.setUpdateTime(new Date());		
				baseDao.saveEntity(baseInfo);
				if((Integer.valueOf(baseInfo.getLoanApplyNo()) % 100) == 0) {
					logger.info("loanBaseInfoTask定时任务运行中，贷款用户的贷款编号--贷款申请NO--借款人姓：[{}]--[{}]--[{}]",
							baseInfo.getBillCode(), baseInfo.getLoanApplyNo(), baseInfo.getCrmotoloanersur());
				}
			}
			pageIndex++;
			if(results.getPageTotal()<=pageIndex) {
				pageIndex=-1;
			}
		}
		/*废弃该方案
		//更新贷款时间和贷款状态
		int pIndex=0;
		int pSize=100;
		String pSql="select t.loan_actual_time ,t.loan_status ,t.bill_code from o2o_loan_oaoverdue t where t.loan_status='Y' ";
		while(pIndex>=0){
			logger.info("loanBaseInfoTask更新货款时间和状态，正在查询第[{}]页数据", pIndex);
			SqlPageQuery<Object[]> PageQuery =new SqlPageQuery<>(Object[].class,pSize,pIndex,pSql);
			PageResult<Object[]> r=serviceDao.findDataWithPageForService(PageQuery);
			List<Object[]> list=r.getData();
			if(list!=null&&list.size()>0){
				for (int i = 0; i < list.size(); i++) {
					Object[] b=list.get(i);
					baseDao.excuteSql("update loan_base_info set loan_time = ?,refund_status = ? where bill_code = ?  ", b[0]!=null?(Date)b[0]:"", b[1]!=null?(String)b[1]:"", b[2]!=null?(String)b[2]:"");
				}
			}
			pIndex++;
			if(r.getPageTotal()<=pIndex){
				pIndex=-1;
			}
		}
		logger.info("loanBaseInfoTask更新货款时间和状态结束", "");
		*/
		
	}
	@Deprecated
	public void saveBaseO2o()  throws Exception{
		String delSql	= "DELETE FROM loan_base_info where source_type='1'";
		baseDao.deleteEntity(delSql);
		
		StringBuffer querySql=new StringBuffer();
		 querySql.append("SELECT ol. bill_code, ol.loan_id AS customer_id, ol.loanapply_no AS loan_apply_no, ol.ltd AS operator_agency, ol.operator_no, ol.operator_name, ol.referrer_no,ol.referrer_name,ol.wf_type as refund_status,"); 
		 querySql.append("ol.bank_loan_name AS bankrefund,ol.bank_type,ol.loan_type,ol.loan_term ,ol.loana_mount as loan_amount,loan.loan_amount as bank_loan_amount, "); 
		 querySql.append("loan.loan_date as loan_time,user.document_type as document_type,user.document_no as idcard,user.user_surname as crmotoloanersur, ");
		 querySql.append("user.user_name as  crmotoloaner ,USER.user_full_name as crmotoloanername,USER.user_phone as crmotoloanerphone,ol.customer_type,ol.is_place_on_file,ol.place_on_file_time,ol.create_date as create_time,ol.end_date as end_date,");
		 querySql.append("ol.loanrate,ol.depositrate as deposite_rate,ol.managefeerate as manger_rate,ol.risk as risk_rate,");
		 querySql.append("ex.rec_deposite as total_deposite ,ex.rec_deposite, ex.red_deposite, ex.reta_deposite,(ex.red_deposite-ex.reta_deposite) as balance_deposite,");
		 querySql.append("ex.rec_manger as total_manger ,ex.rec_manger, ex.red_manger, ex.reta_manage AS reta_manager,(ex.red_manger - reta_manage) AS balance_manger,");
		 querySql.append("ex.rec_risk as total_risk ,ex.rec_risk , ex.red_risk , ex.reta_risk ,(ex.red_risk -ex.reta_risk ) as balance_risk ,");
		 querySql.append("DATE_ADD(loan.loan_date,INTERVAL ol.loan_term month) AS end_bank_date");
		 querySql.append(" from o2o_loanapply ol left join o2o_bank_loan_data loan on ol.bill_code=loan.bill_code");
		 querySql.append(" LEFT JOIN o2o_loanapply_expand ex ON ol.bill_code = ex.bill_code ");
		 querySql.append("LEFT JOIN (select u.document_type,u.document_no,u.user_surname,u.user_name,u.user_full_name,u.user_phone,u.loanapply_no from o2o_loan_user u where u.spouse_id is null GROUP BY u.loanapply_no) USER ON ol.loanapply_no = USER .loanapply_no");
		 querySql.append(" where (ol.data_source IS NULL OR ol.data_source <> '2') and ol.bill_code is not null and loan.pay_type not in ('DF')  order by ol.bill_code");
		int pageIndex=0;
		int pageSize=100;
		while(pageIndex>=0){
			logger.info("loanBaseInfoTask新系统定时任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<LoanBaseInfoEntity> sqlPageQuery = new SqlPageQuery<>(LoanBaseInfoEntity.class, pageSize,pageIndex, querySql.toString());
			PageResult<LoanBaseInfoEntity> results = serviceDao.findDataWithPageForService(sqlPageQuery);
			
			List<LoanBaseInfoEntity> baseInfoList = results.getData();
			for(LoanBaseInfoEntity baseInfo : baseInfoList) {
				if(StringUtil.isNotBlank(baseInfo.getOperatorAgency())){
					
				HrUnit unit= baseDao.findEntity("select hr.PROVINCE from eadb.hr_units hr where hr.unitno=?", HrUnit.class, baseInfo.getOperatorAgency());
				if(unit!=null){
					baseInfo.setOperatorProvince(unit.getProvince());
				}
			}
				
			if(StringUtils.isNotEmpty(baseInfo.getRefundStatus())){
				if("O".equals(baseInfo.getRefundStatus())){
					baseInfo.setRefundStatus("RUN");
				}else if("HN".equals(baseInfo.getRefundStatus())){
					baseInfo.setRefundStatus("CLO");
				}	
			}
				baseInfo.setUpdateTime(new Date());
				baseInfo.setSourceType(1);
				baseInfo.setIslock("N");
				
				baseDao.saveEntity(baseInfo);
				logger.info("[新系统][贷前]更新数据，单号：[{}]，loan apply no:[{}]", baseInfo.getBillCode(), baseInfo.getLoanApplyNo());
			}
			pageIndex++;
			if(results.getPageTotal()<=pageIndex) {
				pageIndex=-1;
			}
		}
		
		String updateSql="UPDATE loan_base_info a " +
			" SET a.couple_name = ( SELECT b.customer_name FROM o2o_customer b WHERE customer_id=" +
			"	(select mate_id from	o2o_customer c where c.customer_id=a.customer_id)" +
			") WHERE a.source_type = 1";
		baseDao.excuteSql(updateSql);
	}
	//续贷
	public void renewLoan(){
		logger.info("开始同步续贷字段=============");
		String pSql=" select t.bill_code,(select count(1) from loan_base_info l where l.idcard= t.idcard and l.loan_time is not null "
				+ "and l.loan_time<=t.loan_time) num from loan_base_info t where t.loan_time is not null  AND t.loan_num = 0 ";
		 List<Object[]> list=baseDao.findObjectsList(pSql);
		 logger.info("续贷更新记录数{}=============",list.size());
		 for (int i = 0; i < list.size(); i++) {
			 Object[] b=list.get(i);
			 baseDao.excuteSql("update loan_base_info set loan_num=? where bill_code=? ", b[1], b[0]);
		 }
		 baseDao.excuteSql("update loan_base_info t set t.is_renew_loan = 'Y' where t.loan_num >1");
		 baseDao.excuteSql("update loan_base_info t set t.is_renew_loan = 'N' where t.loan_num =1 ");
		 logger.info("结束同步续贷字段=============");
	}
	
	//新系统还款方式
	public void repayMentwayByNew() {
		logger.info("（新）开始还款方式字段=============");
		String pSql = " select o.bill_code,o.repay_mentway from o2o_loanapply o where (o.data_source IS NULL OR o.data_source <> '2') AND o.bill_code is not null";
		List<Object[]> list = serviceDao.findObjectsList(pSql);
		logger.info("（新）还款方式更新记录数{}=============", list.size());
		for (int i = 0; i < list.size(); i++) {
			Object[] b = list.get(i);

			/*
			 * List<Object[]> rmList = serviceDao.findParams(
			 * "select o.bill_code,o.repay_mentway from o2o_loanapply o where o.bill_code=?"
			 * ,b[0]);
			 */
			logger.info("（新）贷款编号{}=============", b[0]);
			// Object[] date = rmList.get(0);

			baseDao.excuteSql("update loan_base_info set repay_mentway=? where bill_code=? ", b[1], b[0]);
		}
		logger.info("（新）结束还款方式字段=============");
	}
		
	//旧系统还款方式
	public void repayMentwayByOld() {
		logger.info("（旧）开始还款方式字段=============");
		String pSql = " select o.BILLCODE,o.REPAYMENTWAY from eadb.o2o_loanapply o where o.ISLOCK = 'N' AND o.LOANAPPLYNO_MAIN IS NULL";
		List<Object[]> list = baseDao.findObjectsListByOracle(pSql);
		logger.info("（旧）还款方式更新记录数{}=============", list.size());
		for (int i = 0; i < list.size(); i++) {
			Object[] b = list.get(i);

			/*
			 * List<Object[]> rmList = serviceDao.findParams(
			 * "select o.bill_code,o.repay_mentway from o2o_loanapply o where o.bill_code=?"
			 * ,b[0]);
			 */
			logger.info("（旧）贷款编号{}=============", b[0]);
			// Object[] date = rmList.get(0);

			baseDao.excuteSql("update loan_base_info set repay_mentway=? where bill_code=? ", b[1], b[0]);
		}
		logger.info("（旧）结束还款方式字段=============");
	}

	//老系统放款时间的优化（非BK类型的贷款单）
	public void loanTime(){
		logger.info("开始放款时间的优化=============");
		String sql="select t.bill_code from loan_base_info t where t.loan_type!='BK'  and source_type=0 and t.loan_time is null ";
		List<Object[]> billCodeList=baseDao.findObjectsList(sql);
		for (int i = 0; i < billCodeList.size(); i++) {
			Object[] billCodes = billCodeList.get(i);
			Object billCode = billCodes[0];

			List<Object[]> eaidList = baseDao.findParamsForOracle(
					"select p.eaid, p.payamount from eadb.o2o_pay p where p.billstatus='CLO' and p.loanapplybillcode=? order by p.eaid desc",
					billCode);
			if (!eaidList.isEmpty() && eaidList.size() > 0) {
				Object[] eaids = eaidList.get(0);
				Object eaid = eaids[0];
				Object payamount = eaids[1];
				logger.info("EADI params:billcode:[{}]-eaid[{}]--payamount[{}]", billCode, eaid, payamount);

				List<Object[]> wfpList = baseDao.findParamsForOracle(
						"select max(y.auditordate) from eadb.wf_processtaskhistory y where y.billno=? and y.billtype='OTP' order by y.taskno desc",
						eaid);
				Object[] date = wfpList.get(0);
				baseDao.excuteSql("UPDATE loan_base_info set loan_time =?, bank_loan_amount=?, bankrefund='EA' WHERE bill_code=? and loan_time IS NULL",
						date[0], payamount, billCode);
			}
		}
		logger.info("结束放款时间的优化=============");
	}
	
	public void saveBaseO2oByNew()  throws Exception{
		Calendar cal = Calendar.getInstance();
		String today = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(cal.getTime());
		cal.add(Calendar.DATE, -1);
		String yesterday = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(cal.getTime());
		
//		String delSql	= "DELETE FROM loan_base_info where source_type='1' and end_date > DATE_FORMAT('" + yesterday + "', '%Y-%m-%d %T') AND end_date <= DATE_FORMAT('"+ today + "', '%Y-%m-%d %T')";
//		baseDao.deleteEntity(delSql);
		
		StringBuffer querySql=new StringBuffer();
		
		 querySql.append("SELECT ol. bill_code, ol.loan_id AS customer_id, ol.loanapply_no AS loan_apply_no, ol.ltd AS operator_agency, ol.operator_no, ol.operator_name, ol.referrer_no,ol.referrer_name,ol.wf_type as refund_status,"); 
		 querySql.append("ol.bank_loan_name AS bankrefund,ol.bank_type,ol.loan_type,ol.loan_term ,ol.loana_mount as loan_amount,ol.repay_mentway as repayMentway,ol.end_bank_date as endBankDate,ol.ea_mount AS eaMount,loan.loan_amount as bank_loan_amount, "); 
		 querySql.append("loan.loan_date as loan_time,user.document_type as document_type,user.document_no as idcard,user.user_surname as crmotoloanersur, ");
		 querySql.append("user.user_name as  crmotoloaner ,USER.user_full_name as crmotoloanername,USER.user_phone as crmotoloanerphone,ol.customer_type AS customerType,ol.is_place_on_file AS isPlaceOnFile,ol.place_on_file_time AS place_on_file_time,ol.create_date as create_time,ol.end_date as end_date,");
		 querySql.append("ol.loanrate,ol.depositrate as deposite_rate,ol.managefeerate as manger_rate,ol.risk as risk_rate,");
		 querySql.append("ex.rec_deposite as total_deposite ,ex.rec_deposite, ex.red_deposite, ex.reta_deposite,(ex.red_deposite-ex.reta_deposite) as balance_deposite,");
		 querySql.append("ex.rec_manger as total_manger ,ex.rec_manger, ex.red_manger, ex.reta_manage AS reta_manager,(ex.red_manger - reta_manage) AS balance_manger,");
		 querySql.append("ex.rec_risk as total_risk ,ex.rec_risk , ex.red_risk , ex.reta_risk ,(ex.red_risk -ex.reta_risk ) as balance_risk ,");
		 querySql.append("DATE_ADD(loan.loan_date,INTERVAL ol.loan_term month) AS end_bank_date,ol.repay_mentway AS repay_mentway");
		 querySql.append(" from o2o_loanapply ol left join o2o_bank_loan_data loan on ol.bill_code=loan.bill_code");
		 querySql.append(" LEFT JOIN o2o_loanapply_expand ex ON ol.loanapply_no = ex.loanapply_no ");
		 querySql.append("LEFT JOIN (select u.document_type,u.document_no,u.user_surname,u.user_name,u.user_full_name,u.user_phone,u.loanapply_no from o2o_loan_user u where u.spouse_id is null GROUP BY u.loanapply_no) USER ON ol.loanapply_no = USER .loanapply_no");
//		 querySql.append(" and ol.end_date > DATE_FORMAT('" + yesterday + "', '%Y-%m-%d %T') AND ol.end_date <= DATE_FORMAT('"+ today + "', '%Y-%m-%d %T')");
		 querySql.append(" where (ol.data_source is null or ol.data_source<>'2') and ol.bill_code is not null  AND (loan.pay_type  !='DF' or pay_type is null)  order by ol.bill_code");
		int pageIndex=0;
		int pageSize=100;
		while(pageIndex>=0){
			logger.info("loanBaseInfoTask新系统定时任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<LoanBaseInfoEntity> sqlPageQuery = new SqlPageQuery<>(LoanBaseInfoEntity.class, pageSize,pageIndex, querySql.toString());
			PageResult<LoanBaseInfoEntity> results = serviceDao.findDataWithPageForService(sqlPageQuery);
			
			List<LoanBaseInfoEntity> baseInfoList = results.getData();
			for(LoanBaseInfoEntity baseInfo : baseInfoList) {
				if(StringUtil.isNotBlank(baseInfo.getOperatorAgency())){
					
					HrUnit unit= baseDao.findEntity("select hr.PROVINCE from eadb.hr_units hr where hr.unitno=?", HrUnit.class, baseInfo.getOperatorAgency());
					if(unit!=null){
						baseInfo.setOperatorProvince(unit.getProvince());
					}
				}
				
				if(StringUtils.isNotEmpty(baseInfo.getRefundStatus())){
					if("O".equals(baseInfo.getRefundStatus())){
						baseInfo.setRefundStatus("RUN");
					}else if("HN".equals(baseInfo.getRefundStatus())){
						baseInfo.setRefundStatus("CLO");
					}
				}

				baseInfo.setUpdateTime(new Date());
				baseInfo.setSourceType(1);
				baseInfo.setIslock("N");

				boolean flag;
				StringBuffer update=new StringBuffer(" UPDATE loan_base_info l SET l.end_bank_date=?,l.repay_mentway=?,l.ea_mount =?, l.bank_loan_amount =?, l.loan_time =?, l.bankrefund =?, l.bank_type =?, ");
				update.append(" l.loan_amount =?, l.loan_term=?,l.referrer_name=?,l.total_deposite =?, l.rec_deposite =?, l.red_deposite =?, l.reta_deposite =?, ");
				update.append(" l.balance_deposite =?, l.total_manger =?, l.rec_manger =?, l.red_manger =?, l.reta_manger =?, ");
				update.append(" l.balance_manger =?, l.total_risk =?, l.rec_risk =?, l.red_risk =?, l.reta_risk =?, ");
				update.append(" l.balance_risk =?,l.is_place_on_file=?,l.place_on_file_time=? WHERE l.bill_code =? ");
				flag=baseDao.excuteSql(update.toString(),baseInfo.getEndBankDate(),baseInfo.getRepayMentway(),baseInfo.getEaMount(),baseInfo.getBankLoanAmount(), baseInfo.getLoanTime(), baseInfo.getBankrefund(),
						baseInfo.getBankType(), baseInfo.getLoanAmount(),baseInfo.getLoanTerm(),baseInfo.getReferrerName(), baseInfo.getTotalDeposite(), baseInfo.getRecDeposite(), baseInfo.getRedDeposite(), baseInfo.getRetaDeposite(),
						baseInfo.getBalanceDeposite(), baseInfo.getTotalManger(), baseInfo.getRecManger(), baseInfo.getRedManger(), baseInfo.getRetaManger(), baseInfo.getBalanceManger(),
						baseInfo.getTotalRisk(), baseInfo.getRecRisk(), baseInfo.getRedRisk(), baseInfo.getRetaRisk(), baseInfo.getBalanceRisk(),
						baseInfo.getIsPlaceOnFile(),baseInfo.getPlaceOnFileTime(),baseInfo.getBillCode());
				if(flag == false){
					baseDao.saveEntity(baseInfo);
				}
			}

			pageIndex++;
			if(results.getPageTotal()<=pageIndex) {
				pageIndex=-1;
			}
		}
		
		String updateSql="UPDATE loan_base_info a " +
			" SET a.couple_name = ( SELECT b.customer_name FROM o2o_customer b WHERE customer_id=" +
			"	(select mate_id from	o2o_customer c where c.customer_id=a.customer_id)" +
			") WHERE a.source_type = 1";
		baseDao.excuteSql(updateSql);
	}
	
	
	//只更新贷前表里没有的数据
	public void saveBaseO2oByNewNotIn() throws Exception{
		Calendar cal = Calendar.getInstance();
		String today = new SimpleDateFormat("yyyy-MM-dd ").format(cal.getTime());
		cal.add(Calendar.DATE, -1);
		String yesterday = new SimpleDateFormat("yyyy-MM-dd ").format(cal.getTime());
		
		String devSql	= "select bill_code from loan_base_info where source_type='1' and end_date > DATE_FORMAT('" + yesterday + "', '%Y-%m-%d') AND end_date <= DATE_FORMAT('" + today + "', '%Y-%m-%d')";
		List<String> devCodelist=baseDao.findSimpleObjectList(devSql, String.class);
		
		String serviceSql = "select bill_code from o2o_loanapply where (data_source IS NULL OR data_source <> '2') and end_date > DATE_FORMAT('" + yesterday + "', '%Y-%m-%d') AND end_date <= DATE_FORMAT('" + today + "', '%Y-%m-%d')";
		List<String> serviceCodeList = serviceDao.findSimpleObjectList(serviceSql, String.class);
		
		boolean notInCode=serviceCodeList.removeAll(devCodelist);   //从serviceCodeList中移除与devSql相同的元素
		
		if(CollectionUtils.isEmpty(serviceCodeList)){
			logger.info("[新系统][贷前]今天没有新的数据！");
			return;
		}
		if(CollectionUtils.isEmpty(devCodelist)){
			notInCode=true;
		}
		logger.info("[新系统][贷前]需要同步贷款单单号：[{}]", serviceCodeList.toString());
		
		Iterator<String> code = serviceCodeList.iterator();   //创建迭代器
		StringBuffer addCode=new StringBuffer();
		while (code.hasNext()) {       //循环遍历迭代器
			addCode.append("'"+code.next()+"'");
			if(code.hasNext()){   //如果还有下一个元素则加，
				addCode.append(",");
			 }
		}
		logger.info("[新系统][贷前]addCode:[{}]", addCode);
		
		if(notInCode==true){
		
		StringBuffer querySql=new StringBuffer();
		
		 querySql.append("SELECT ol. bill_code, ol.loan_id AS customer_id, ol.loanapply_no AS loan_apply_no, ol.ltd AS operator_agency, ol.operator_no, ol.operator_name, ol.referrer_no,ol.referrer_name, ol.wf_type as refund_status,"); 
		 querySql.append("ol.bank_loan_name AS bankrefund,ol.bank_type,ol.loan_type,ol.loan_term ,ol.loana_mount as loan_amount,loan.loan_amount as bank_loan_amount, "); 
		 querySql.append("loan.loan_date as loan_time,user.document_type as document_type,user.document_no as idcard,user.user_surname as crmotoloanersur, ");
		 querySql.append("user.user_name as  crmotoloaner ,ol.customer_type,ol.is_place_on_file,ol.place_on_file_time,ol.create_date as create_time,ol.end_date as end_date,");
		 querySql.append("ol.loanrate,ol.depositrate as deposite_rate,ol.managefeerate as manger_rate,ol.risk as risk_rate,");
		 querySql.append("ex.rec_deposite as total_deposite ,ex.rec_deposite, ex.red_deposite, ex.reta_deposite,(ex.red_deposite-ex.reta_deposite) as balance_deposite,");
		 querySql.append("ex.rec_manger as total_manger ,ex.rec_manger, ex.red_manger, ex.reta_manage AS reta_manager,(ex.red_manger - reta_manage) AS balance_manger,");
		 querySql.append("ex.rec_risk as total_risk ,ex.rec_risk , ex.red_risk , ex.reta_risk ,(ex.red_risk -ex.reta_risk ) as balance_risk ,");
		 querySql.append("DATE_ADD(loan.loan_date,INTERVAL ol.loan_term month) AS end_bank_date");
		 querySql.append(" from o2o_loanapply ol left join o2o_bank_loan_data loan on ol.bill_code=loan.bill_code");
		 querySql.append(" LEFT JOIN o2o_loanapply_expand ex ON ol.bill_code = ex.bill_code ");
		 querySql.append("LEFT JOIN (select u.document_type,u.document_no,u.user_surname,u.user_name,u.user_full_name,u.user_phone,u.loanapply_no from o2o_loan_user u where u.spouse_id is null GROUP BY u.loanapply_no) USER ON ol.loanapply_no = USER .loanapply_no");
		 querySql.append(" where (ol.data_source IS NULL OR ol.data_source <> '2') AND ol.bill_code is not null ");		 
		 querySql.append(" and ol.bill_code in(");
		 querySql.append(addCode);
		 querySql.append(") order by ol.bill_code");
		 
		int pageIndex=0;
		int pageSize=100;
		while(pageIndex>=0){
			logger.info("loanBaseInfoTask新系统定时任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<LoanBaseInfoEntity> sqlPageQuery = new SqlPageQuery<>(LoanBaseInfoEntity.class, pageSize,pageIndex, querySql.toString());
			PageResult<LoanBaseInfoEntity> results = serviceDao.findDataWithPageForService(sqlPageQuery);
			
			List<LoanBaseInfoEntity> baseInfoList = results.getData();
			for(LoanBaseInfoEntity baseInfo : baseInfoList) {
				if(StringUtil.isNotBlank(baseInfo.getOperatorAgency())){
					
				HrUnit unit= baseDao.findEntity("select hr.PROVINCE from eadb.hr_units hr where hr.unitno=?", HrUnit.class, baseInfo.getOperatorAgency());
				if(unit!=null){
					baseInfo.setOperatorProvince(unit.getProvince());
				}
			}
				
			if(StringUtils.isNotEmpty(baseInfo.getRefundStatus())){
				if("O".equals(baseInfo.getRefundStatus())){
					baseInfo.setRefundStatus("RUN");
				}else if("HN".equals(baseInfo.getRefundStatus())){
					baseInfo.setRefundStatus("CLO");
				}	
			}
				baseInfo.setUpdateTime(new Date());
				baseInfo.setSourceType(1);
				baseInfo.setIslock("N");
				
				baseDao.saveEntity(baseInfo);
				logger.info("[新系统][贷前]增量更新数据，单号：[{}]，loan apply no:[{}]", baseInfo.getBillCode(), baseInfo.getLoanApplyNo());
			}
			pageIndex++;
			if(results.getPageTotal()<=pageIndex) {
				pageIndex=-1;
			}
		}
		
		String updateSql="UPDATE loan_base_info a " +
			" SET a.couple_name = ( SELECT b.customer_name FROM o2o_customer b WHERE customer_id=" +
			"	(select mate_id from	o2o_customer c where c.customer_id=a.customer_id)" +
			") WHERE a.source_type = 1";
		baseDao.excuteSql(updateSql);
	}else{
 		logger.info("贷前没有新的数据可以更新！");
	}
	}
	
	//更新归档状态
	public void isPlaceOnFile(){
		String queryDevSql = "SELECT * FROM loan_base_info l WHERE l.bill_code LIKE '%O2O%' AND l.islock = 'N'";
		String updateSql = "update loan_base_info set is_place_on_file=? where bill_code=?";
		int pageIndex = 0;
		int pageSize = 100;
		while (pageIndex >= 0) {
			logger.info("更新归档状态任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<LoanBaseInfoEntity> sqlPageQuery = new SqlPageQuery<>(LoanBaseInfoEntity.class, pageSize,
					pageIndex, queryDevSql);
			PageResult<LoanBaseInfoEntity> results = baseDao.findDataWithPageByMySql(sqlPageQuery);

			List<LoanBaseInfoEntity> baseInfoList = results.getData();
			for (LoanBaseInfoEntity baseInfo : baseInfoList) {
				String sql = "SELECT t.billstatus as is_place_on_file,r.loadapplycode as bill_code FROM eadb.o2o_refundcontract t LEFT JOIN eadb.o2o_refund r ON t.refundno = r.refundno WHERE t.isfrom = 'CON' and  r.islock='N' and t.islock='N' and r.loanapplyno=?";
				LoanBaseInfoEntity entity = baseDao.findEntity(sql, LoanBaseInfoEntity.class,
						baseInfo.getLoanApplyNo());
				if (entity != null) {
					baseDao.excuteSql(updateSql,entity.getIsPlaceOnFile(), entity.getBillCode());// 更新归档状态
				}
				//将String转换成int，当取余100等于零时
				if ((Integer.valueOf(baseInfo.getLoanApplyNo()) % 100) == 0) {
					logger.info("更新归档状态任务运行中，贷款用户的贷款编号：[{}]", baseInfo.getBillCode());
				}
			}
			pageIndex++;
			if (results.getPageTotal() <= pageIndex) {
				pageIndex = -1;
			}
		}
		logger.info("更新归档状态任务结束");
	}
}
